USE [comunion]
GO

/****** Object:  View [dbo].[V_ProductBalanceInquiry_ModuleWPartNo]    Script Date: 07/05/2010 13:21:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO




--Product balance inquiry for Product Module
--V_ProductBalanceInquiry_Module
--2003.12.11
--logicglass inc.

CREATE  VIEW [dbo].[V_ProductBalanceInquiry_ModuleWPartNo] AS

SELECT 
A.cCompanyID, A.cItemNo, 
ISNULL(SUM(A.nInitial), 0) AS nInitial, 
ISNULL(SUM(A.nIncoming), 0) AS nIncoming, 
ISNULL(SUM(A.nOutgoing), 0) AS nOutgoing, 
ISNULL(SUM(A.nRetSales), 0) AS nRetSales, 
ISNULL(SUM(A.nRetPur), 0) AS nRetPur, 
ISNULL(SUM(A.nIssues), 0) AS nIssues, 
ISNULL(SUM(A.nBack), 0) AS nBack, 
ISNULL(SUM(A.nProdOut), 0) AS nProdOut, 
ISNULL(SUM(A.nProdIn), 0) AS nProdIn, 
ISNULL(SUM(A.nReserved), 0) AS nReserved, 
ISNULL(SUM(A.nAdjustment), 0) AS nAdjustment,
ISNULL(SUM(A.nSampleDemo), 0) AS nSampleDemo,
a.cBarcodeID FROM
(
(
--Initial
SELECT A.cCompanyID, A.cItemNo, 
SUM(A.nInitial) AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,A.cPartNo AS cBarcodeID
FROM ITEM A 
GROUP BY A.cCompanyID, A.cItemNo, a.cPartNo
)
UNION ALL
(
--Incoming
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, SUM(A.nQty) AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,A.cBarcodeID
FROM WRR_T A 
LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType = 'Purchase' AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Outgoing
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, SUM(A.nQty) AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,A.cBarcodeID
FROM SALES_T A 
LEFT OUTER JOIN SALES B ON A.cInvNo = B.cInvNo AND A.cCompanyID = B.cCompanyID
WHERE  B.lCancelled = 0 AND B.cType IN ('Invoice', 'Invoice w/ DR', 'Sample/Demo', 'Consignment') AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Sales Return
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, SUM(A.nQty) AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,A.cBarcodeID
FROM WRR_T A 
LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType = 'Sales Return' AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Purchase Return
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, SUM(A.nQty) AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM DR_T A 
LEFT OUTER JOIN DR B ON A.cDRNo = B.cDRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType = 'Purchase Return' AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Issues ('Others', 'Project')
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, SUM(A.nQty) AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM DR_T A 
LEFT OUTER JOIN DR B ON A.cDRNo = B.cDRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Others', 'Project') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Sample/Demo
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, SUM(A.nQty) AS nSampleDemo
,a.cBarcodeID
FROM DR_T A 
LEFT OUTER JOIN DR B ON A.cDRNo = B.cDRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Sample', 'Demo') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Sample/Demo with sales
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, SUM(A.nQty) AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, SUM(-1 * A.nQty) AS nSampleDemo
,a.cBarcodeID
FROM SALES_T A 
LEFT OUTER JOIN SALES B ON A.cInvNo = B.cInvNo AND A.cCompanyID = B.cCompanyID
WHERE  B.lCancelled = 0 AND B.cType IN ('Sample/Demo') AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Returned from Demo/Sample (nSampleDemo)
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, SUM(-1 * A.nQty) AS nSampleDemo
,a.cBarcodeID
FROM WRR_T A 
LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Sample', 'Demo') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Issues (DR Issues without invoice)
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, SUM(A.nQty) AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM DR_T A 
LEFT OUTER JOIN DR B ON A.cDRNo = B.cDRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Issues') AND ISNULL(B.cInvNo, '') = '' AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Back 
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, SUM(A.nQty) AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM WRR_T A 
LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('For Replacement', 'Prod. Return', 'Others') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Back (DR Replacement)
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, SUM(-1 * A.nQty) AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM DR_T A 
LEFT OUTER JOIN DR B ON A.cDRNo = B.cDRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Replacement') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Production Out
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, SUM(A.nQty) AS nProdOut, 0 AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM DR_T A 
LEFT OUTER JOIN DR B ON A.cDRNo = B.cDRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Production-OS', 'Production-Int') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Production In
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, SUM(A.nQty) AS nProdIn, 0 AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM WRR_T A 
LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Production') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Reserved
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, (sum(A.nQty) - sum(ISNULL(C.nDelivered, 0))) AS nReserved, 0 AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM SO_T A 
LEFT OUTER JOIN SO B ON A.cSONo = B.cSONo AND A.cCompanyID = B.cCompanyID
LEFT OUTER JOIN V_DeliveredSOItem C ON A.nIdentity = C.nRefIdentity AND A.cItemNo = C.cItemNo AND A.cCompanyID = C.cCompanyID
WHERE B.lServed = 0 AND B.lCApproved = 1 AND B.lPApproved = 1 AND B.lCancelled = 0 AND A.lDRReady = 1 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo, A.nQty, C.nDelivered,a.cBarcodeID
)
UNION ALL
(
--Inventory Adjustment
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, SUM(nCount - nBalance) AS nAdjustment, 0 AS nSampleDemo
,NULL AS cBarcodeID
FROM INVADJ_T A 
LEFT OUTER JOIN INVADJ B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo
)
UNION ALL
(
--Inventory Adjustment (Damage Module)
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, SUM(A.nQty * -1) AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM DAMAGED_T A 
LEFT OUTER JOIN DAMAGED B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE B.lClosed = 0 AND B.lCancelled = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Inventory Adjustment (Damage Module - Damaged Item Code)
SELECT A.cCompanyID, A.cItemAccount AS cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, SUM(A.nQty) AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM DAMAGED_T A 
LEFT OUTER JOIN DAMAGED B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE B.lClosed = 0 AND B.lCancelled = 0 AND A.lWriteOff = 0
GROUP BY A.cCompanyID, A.cItemAccount,a.cBarcodeID
)
UNION ALL
(
--Inventory Adjustment (DR Consignment & Transfer)
SELECT A.cCompanyID, A.cItemNo,
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, SUM(-1*A.nQty) AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM DR_T A 
LEFT OUTER JOIN DR B ON A.cDRNo = B.cDRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Consignment', 'Transfer') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)
UNION ALL
(
--Inventory Adjustment (WRR Consignment & Transfer)
SELECT A.cCompanyID, A.cItemNo, 
0 AS nInitial, 0 AS nIncoming, 0 AS nOutgoing, 0 AS nRetSales, 0 AS nRetPur, 0 AS nIssues, 0 AS nBack, 0 AS nProdOut, 0 AS nProdIn, 0 AS nReserved, SUM(A.nQty) AS nAdjustment, 0 AS nSampleDemo
,a.cBarcodeID
FROM WRR_T A 
LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType IN ('Consignment', 'Transfer') AND B.lCancelled = 0 AND B.lClosed = 0
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID
)) A
LEFT OUTER JOIN ITEM B ON A.cItemNo = B.cItemNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType <> 'Labor'
GROUP BY A.cCompanyID, A.cItemNo,a.cBarcodeID





GO


